package com.yskj.mwk.dao;


import com.yskj.mwk.bean.*;
import org.apache.ibatis.annotations.*;
import com.github.pagehelper.Page;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

public interface UserMapper {
    @Select("select * from users")
    List<User> getUsers();

    //获取小程序accesstoken
    @Select("select * from xcxconfig")
    Xcxconfig getAccessToken();

    //修改小程序accesstoken
    @Update("UPDATE xcxconfig SET access_token = #{access_token},updateTimestamp = #{updateTimestamp}")
    int updateAccessToken(@Param("access_token") String access_token, @Param("updateTimestamp") Long updateTimestamp);

    //判断openid是否存在
    @Select("select count(1) from users where openid=#{openid}")
    int checkOpenid(@Param("openid") String openid);

    //新增openid到user表
    @Insert("INSERT INTO users (openid,createTime) VALUES (#{openid},#{createTime})")
    int insertOpenidToUser(@Param("openid") String openid,@Param("createTime") String createTime);



    //根据手机号获取用户信息
    @Select("select * from users where tell = #{tell}")
    User getUsersByTell(@Param("tell") String tell);

    //关联openid和手机号
    @Update("UPDATE users SET isBindTell=1, tell = #{user.tell},name = #{user.name},uuid=#{user.uuid} where openid=#{user.openid}")
    int insertUser(@Param("user") User user);

    //把手机号绑定的openid跟新下，以防用户删除过小程序
    @Update("UPDATE users SET openid=#{openid} where tell=#{tell}")
    int updateOpenidByTell(@Param("openid") String openid,@Param("tell") String tell);

    //根据手机号获取用户信息
    @Select("select uuid,isBindTell from users where openid=#{openid}")
    Map<String,Object> getUserUuid(@Param("openid") String openid);

    //根据手机号获取用户信息
    @Select("select * from users where uuid = #{uuid}")
    User getUserDescByUuid(@Param("uuid") String uuid);

    //把手机号绑定的openid跟新下，以防用户删除过小程序
    @Update("UPDATE users SET name=#{name},headUrl=#{headUrl} where uuid=#{uuid}")
    int updateUserInfo(@Param("name") String name,@Param("headUrl") String headUrl,@Param("uuid") String uuid);

    /**
     * 地址
     * */
    //根据id修改地址
    @Update("UPDATE address SET lxrname=#{address.lxrname},lxrtell=#{address.lxrtell},lxraddress=#{address.lxraddress},province=#{address.province},city=#{address.city},district=#{address.district},isDefault=#{address.isDefault} where id=#{address.id}")
    int updateAddressById(@Param("address") Address address);

    //新增地址到uuid
    @Insert("INSERT INTO address (lxrname,lxrtell,lxraddress,province,city,district,isDefault,uuid) VALUES (#{address.lxrname},#{address.lxrtell},#{address.lxraddress},#{address.province},#{address.city},#{address.district},#{address.isDefault},#{address.uuid})")
    int insertAddress(@Param("address") Address address);

    //如果设置默认那其他的都得设置为不默认
    @Update("UPDATE address SET isDefault=0 where uuid=#{uuid}")
    int updateAddressNoDefault(@Param("uuid") String uuid);

    //删除地址
    @Delete("DELETE FROM address WHERE id=#{id}")
    int deleteAddressById(int id);

    //获取用户所有地址
    @Select("select * from address where uuid = #{uuid} order by isDefault desc")
    List<Address> getAllAddressByUuid(@Param("uuid") String uuid);

    //获取地址详情
    @Select("select * from address where id = #{id}")
    Address getAddressDesc(@Param("id") int id);

    //获取用户默认地址
    @Select("select * from address where uuid = #{uuid} and isDefault=1")
    Address getDefaultAddressDescByUuid(@Param("uuid") String uuid);

    //设置默认地址
    @Update("UPDATE address SET isDefault=1 where id=#{id}")
    int updateAddressDefault(@Param("id") int id);

    /**
     * 获取分类
     * */
    //获取一级分类
    @Select("select * from sort where type = 1 and isShow=1 and isDelete=0 order by level desc")
    List<Sort> getOneLevelSort();

    //获取二级分类
    @Select("select * from sort where type = 2 and isShow=1 and isDelete=0 and parentId=#{parentId} order by level desc")
    List<Sort> getSecondLevelSort(@Param("parentId") int parentId);

    //搜索名字为某某某的商品
    @Select("SELECT * FROM goods_spu WHERE isDelete=0 and publishStatus=1 and spuName LIKE CONCAT('%', #{name}, '%') order by level desc,createTime desc")
    Page<Goods> selectGoodsByNamePageList(@Param("name") String name, @Param("pageNum") int pageNum, @Param("pageSize") int pageSize);

    //显示新品速递商品--每日精选
    @Select("SELECT * FROM goods_spu WHERE isDelete=0 and publishStatus=1 and isJingxuan=1 order by level desc,createTime desc")
    Page<Goods> getIndexNewGoods1(@Param("pageNum") int pageNum,@Param("pageSize") int pageSize);

    //显示新品速递商品--最新推荐
    @Select("SELECT * FROM goods_spu WHERE isDelete=0 and publishStatus=1 and isNew=1 order by level desc,createTime desc")
    Page<Goods> getIndexNewGoods2(@Param("pageNum") int pageNum,@Param("pageSize") int pageSize);

    //显示新品速递商品--现时秒杀
    @Select("SELECT * FROM goods_spu WHERE isDelete=0 and publishStatus=1 and isMiaosha=1 order by level desc,createTime desc")
    Page<Goods> getIndexNewGoods3(@Param("pageNum") int pageNum,@Param("pageSize") int pageSize);

    //显示热门推荐商品
    @Select("SELECT * FROM goods_spu WHERE isDelete=0 and publishStatus=1 and isPopular=1 order by level desc,createTime desc")
    Page<Goods> getIndexPopularGoods(@Param("pageNum") int pageNum,@Param("pageSize") int pageSize);

    //获取一级分类下全部商品
    @Select("SELECT * FROM goods_spu WHERE isDelete=0 and publishStatus=1 and sort1Id=#{sortId1} order by level desc,createTime desc")
    Page<Goods> getALLGoodsBySortId1(@Param("pageNum") int pageNum,@Param("pageSize") int pageSize,@Param("sortId1") int sortId1);

    //获取二级分类下全部商品
    @Select("SELECT * FROM goods_spu WHERE isDelete=0 and publishStatus=1 and sort2Id=#{sortId2} order by level desc,createTime desc")
    Page<Goods> getALLGoodsBySortId2(@Param("pageNum") int pageNum,@Param("pageSize") int pageSize,@Param("sortId2") int sortId2);

    //获取我的收藏
    @Select("SELECT c.*,g.spuDesc,g.spuName,g.price,g.goodsPic,g.publishStatus,g.isDelete FROM users_collection as c LEFT JOIN goods_spu as g on c.spuId=g.spuId WHERE c.uuid=#{uuid} and g.isDelete=0 ORDER BY c.collectTime DESC")
    Page<CollectGoods> getMyCollectedGoods(@Param("pageNum") int pageNum,@Param("pageSize") int pageSize,@Param("uuid") String uuid);

    //取消收藏
    @Delete("DELETE FROM users_collection WHERE spuId=#{id} and uuid=#{uuid}")
    int cancelCollectionById(@Param("id") int id, @Param("uuid") String uuid);

    //新增收藏
    @Insert("INSERT INTO users_collection (uuid,spuId,collectTime,collectPrice) VALUES (#{uuid},#{spuId},#{collectTime},#{collectPrice})")
    int addCollectionById(@Param("uuid") String uuid,@Param("spuId") int spuId,@Param("collectTime") String collectTime,@Param("collectPrice") BigDecimal collectPrice);

    // 获取商品spu主表
    @Select("SELECT * FROM goods_spu WHERE spuId=#{id}")
    Goods getGoodsSpu(@Param("id") int id);

    // 获取商品spu规格属性参数
    @Select("SELECT spec_template FROM goods_spu_detail WHERE spuId=#{id}")
    String getGoodsSpuGuige(@Param("id") int id);

    // 获取商品的图片
    @Select("SELECT picUrl FROM goods_spu_images WHERE spuId=#{id} and type=#{type} order by level desc")
    List<String> getGoodsPicsByType(@Param("id") int id,@Param("type") int type);

    //获取进入编辑diy页的提示图
    @Select("SELECT picUrl FROM goods_spu_images WHERE spuId=#{id} and type=3")
    String getGoodsTipPic(@Param("id") int id);

    //判断商品用户是否收藏
    @Select("SELECT count(1) FROM users_collection WHERE spuId=#{id} and uuid=#{uuid}")
    int checkGoodsIsCollect(@Param("id") int id,@Param("uuid") String uuid);

    //获取对应sku信息
    @Select("SELECT * FROM goods_sku WHERE spuId=#{id} and indexes=#{index}")
    GoodsSku getGoodsSkuByIndexStr(@Param("id") int id,@Param("index") String index);

    @Select("SELECT * FROM goods_sku WHERE skuId=#{skuId}")
    GoodsSku getGoodsSkuBySkuId(@Param("skuId") int skuId);

    //获取商品的sku合集spec
    @Select("SELECT spec_template FROM goods_spu_detail WHERE spuId=#{id}")
    String getGoodsSkuSpecById(@Param("id") int id);

    //新增sku列表
    @Insert("INSERT INTO goods_sku (spuId,sort1Id,sort2Id,indexes,own_spec,createTime) VALUES (#{addSkuBean.spuId},#{addSkuBean.sort1Id},#{addSkuBean.sort2Id},#{addSkuBean.indexes},#{addSkuBean.own_spec},#{addSkuBean.createTime})")
    @Options(useGeneratedKeys = true, keyProperty = "skuId")
    int addSkuSpec(@Param("addSkuBean") AddSkuBean addSkuBean);

    //同步库存表
    @Insert("INSERT INTO goods_sku_stock (skuId) VALUES (#{id})")
    int addSkuStock(@Param("id") int id);

    //添加商品到购物车
    @Insert("INSERT INTO car (spuId,skuId,indexes,own_spec,num,uuid,minNum,createTime,createTimestamp,supplierId) VALUES (#{car.spuId},#{car.skuId},#{car.indexes},#{car.own_spec},#{car.num},#{car.uuid},#{car.minNum},#{car.createTime},#{car.createTimestamp},#{car.supplierId})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int addGoodsInCar(@Param("car") Car car);

    //删除购物车中的商品
    @Delete("DELETE FROM car WHERE id=#{id}")
    int deleteGoodsInCarById(int id);

    //获取购物车列表
    @Select("SELECT c.*,g.salesPrice,g.underlinedPrice,g.skuPic,s.spuName,s.publishStatus,g.enable from car c LEFT JOIN goods_sku g on c.skuId=g.skuId LEFT JOIN goods_spu s on s.spuId=c.spuId where c.uuid=#{uuid} order by c.createTime desc")
    Page<Car> getMyCarGoods(@Param("pageNum") int pageNum,@Param("pageSize") int pageSize,@Param("uuid") String uuid);

    //获取购物车单一商品详情
    @Select("SELECT c.*,g.salesPrice,g.underlinedPrice,g.skuPic,s.spuName,s.publishStatus,g.enable from car c LEFT JOIN goods_sku g on c.skuId=g.skuId LEFT JOIN goods_spu s on s.spuId=c.spuId where c.id=#{id}")
    Car getMyCarGoodsDesc(@Param("id") int id);

    //修改购物车的商品数量
    @Update("UPDATE car SET num=#{num} where id=#{id}")
    int updateGoodsNumInCar(@Param("id") int id,@Param("num") int num);

    //修改购物车的商品规格
    @Update("UPDATE car SET skuId=#{skuId},indexes=#{indexes},own_spec=#{own_spec} where id=#{id}")
    int updateGoodsGuigeInCar(@Param("id") int id,@Param("skuId") int skuId,@Param("indexes") String indexes,@Param("own_spec") String own_spec);

    //创建用于支付的系统订单
    @Insert("INSERT INTO system_order (systemId,createTime,uuid,receiveName,receiveTell,receiveAddress,addressId,totalPrice) VALUES (#{systemOrder.systemId},#{systemOrder.createTime},#{systemOrder.uuid},#{systemOrder.receiveName},#{systemOrder.receiveTell},#{systemOrder.receiveAddress},#{systemOrder.addressId},#{systemOrder.totalPrice})")
    int addSystemOrder(@Param("systemOrder") SystemOrder systemOrder);

    //修改系统订单应支付总额
    @Update("UPDATE system_order SET totalPrice=#{totalPrice} where systemId=#{systemId}")
    void updateSystemOrderPrice(@Param("totalPrice") BigDecimal totalPrice,@Param("systemId") String systemId);

    //获取下单的商品有哪些供应商
    @Select({
            "<script>",
            "SELECT supplierId, GROUP_CONCAT(id) AS ids ",
            "FROM car ",
            "WHERE uuid=#{uuid} AND id IN ",
            "<foreach item='item' index='index' collection='ids' open='(' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            "GROUP BY supplierId",
            "</script>"
    })
    List<SupplierIdsDTO> getSupplierIdByIds(@Param("ids") List<Integer> idsList,@Param("uuid") String uuid);

    //查看库存
    @Select("SELECT stock FROM goods_sku_stock WHERE skuId=#{id}")
    int getStock(@Param("id") int id);

    //新增商品列表
    @Insert("INSERT INTO orderdesc (spuId,skuId,carId,goodsPic,goodsName,goodsSpec,unitPrice,num,subtotal,orderId,supplierId,orderTime) VALUES (#{oDesc.spuId},#{oDesc.skuId},#{oDesc.carId},#{oDesc.goodsPic},#{oDesc.goodsName},#{oDesc.goodsSpec},#{oDesc.unitPrice},#{oDesc.num},#{oDesc.subtotal},#{oDesc.orderId},#{oDesc.supplierId},#{oDesc.orderTime})")
    @Options(useGeneratedKeys = true, keyProperty = "oid")
    void addOrderDesc(@Param("oDesc") OrderDesc oDesc);

    @Update("update editimage set orderId=#{orderId},oid=#{oid} where carid=#{carid}")
    int updateEditImageOrderId(@Param("orderId") String orderId,@Param("oid") int oid,@Param("carid") String carid);

    //新增用户订单表（租户）
    @Insert("INSERT INTO orders (orderId,createTime,receiveName,receiveTell,receiveAddress,addressId,totalPrice,shippingFee,supplierId,systemId,uuid,yujifahuo,lastUpdateTime) VALUES (#{order.orderId},#{order.createTime},#{order.receiveName},#{order.receiveTell},#{order.receiveAddress},#{order.addressId},#{order.totalPrice},#{order.shippingFee},#{order.supplierId},#{order.systemId},#{order.uuid},#{order.yujifahuo},#{order.createTime})")
    void addOrder(@Param("order") Order order);

    //获取我的订单页面
    @Select("SELECT o.*,GROUP_CONCAT(d.goodsPic SEPARATOR ',') AS images,GROUP_CONCAT(d.oid SEPARATOR ',') AS oids,COUNT(d.oid) AS odescNum,GROUP_CONCAT(d.goodsName SEPARATOR ';') AS gname from orders o LEFT JOIN orderdesc d on o.orderId=d.orderId WHERE o.uuid=#{uuid} AND orderStatus=#{orderStatus} GROUP BY o.orderId order by o.createTime desc")
    Page<MyOrder> getMyOrdersByOrderStatus(@Param("uuid") String uuid,@Param("orderStatus") int orderStatus,@Param("pageNum") int pageNum,@Param("pageSize") int pageSize);

    //获取售后订单
    @Select("SELECT o.*,GROUP_CONCAT(d.goodsPic SEPARATOR ',') AS images,GROUP_CONCAT(d.oid SEPARATOR ',') AS oids,COUNT(d.oid) AS odescNum,GROUP_CONCAT(d.goodsName SEPARATOR ';') AS gname from orders o LEFT JOIN orderdesc d on o.orderId=d.orderId WHERE o.uuid=#{uuid} AND orderStatus=5 AND (afterStatus=1 or afterStatus=2 or afterStatus=4) GROUP BY o.orderId order by o.createTime desc")
    Page<MyOrder> getMyAfterOrders(@Param("uuid") String uuid,@Param("pageNum") int pageNum,@Param("pageSize") int pageSize);

    //获取我的订单页面全部数据
    @Select("SELECT o.*,GROUP_CONCAT(d.goodsPic SEPARATOR ',') AS images,GROUP_CONCAT(d.oid SEPARATOR ',') AS oids,COUNT(d.oid) AS odescNum,GROUP_CONCAT(d.goodsName SEPARATOR ';') AS gname from orders o LEFT JOIN orderdesc d on o.orderId=d.orderId WHERE o.uuid=#{uuid} AND (orderStatus=1 or orderStatus=2 or orderStatus=3 or orderStatus=4 or orderStatus=6) GROUP BY o.orderId order by o.createTime desc")
    Page<MyOrder> getMyAllOrders(@Param("uuid") String uuid,@Param("pageNum") int pageNum,@Param("pageSize") int pageSize);

    //根据订单id获取订单信息
    @Select("SELECT * FROM orders WHERE orderId=#{orderId}")
    Order getOrderByOrderId(@Param("orderId") String orderId);

    //根据订单id获取订单详情信息
    @Select("SELECT * FROM orderdesc WHERE orderId=#{orderId}")
    List<OrderDesc> getOrderDescByOrderId(@Param("orderId") String orderId);

    //根据微信支付侧订单编号获取有多少订单未发货的
    @Select("SELECT count(1) FROM orders WHERE transaction_id = #{transaction_id} AND (trackingNo IS NULL OR trackingNo = '')")
    int getNoFahuoNum(@Param("transaction_id") String transaction_id);

    //修改订单状态
    @Update("UPDATE orders SET orderStatus=#{orderStatus} where orderId=#{orderId}")
    int updateOrderStatus(@Param("orderId") String orderId,@Param("orderStatus") int orderStatus);

    //修改订单状态 售后状态
    @Update("UPDATE orders SET afterStatus=#{afterStatus} where orderId=#{orderId}")
    int updateAfterOrderStatus(@Param("orderId") String orderId,@Param("afterStatus") int afterStatus);

    //新增意见反馈
    @Insert("INSERT INTO feedback (uuid,question,createTime) VALUES (#{feedback.uuid},#{feedback.question},#{feedback.createTime})")
    int addFeedback(@Param("feedback") Feedback feedback);

    //根据订单编号获取订单金额、
    @Select("SELECT totalPrice FROM system_order WHERE systemId=#{orderId}")
    double getSystemOrderMoney(@Param("orderId") String orderId);

    //根据订单编号获取订单金额、
    @Select("SELECT totalPrice FROM orders WHERE orderId=#{orderId}")
    double getOrderMoney(@Param("orderId") String orderId);

    //修改系统订单状态
    @Update("UPDATE system_order SET transaction_id=#{transaction_id},payStatus=1 where systemId=#{orderId}")
    int updatesysOrderIsPay(@Param("orderId") String orderId,@Param("transaction_id") String transaction_id);

    //修改租户订单状态
    @Update("UPDATE orders SET out_trade_no=#{out_trade_no},payTime=#{payTime},transaction_id=#{transaction_id},orderStatus=2,payStatus=1 where systemId=#{orderId}")
    int updatesplOrderIsPay(@Param("orderId") String orderId,@Param("transaction_id") String transaction_id,@Param("payTime") String payTime,@Param("out_trade_no") String out_trade_no);

    //修改租户订单状态
    @Update("UPDATE orders SET out_trade_no=#{out_trade_no},payTime=#{payTime},transaction_id=#{transaction_id},orderStatus=2,payStatus=1 where orderId=#{orderId}")
    int updatesplOrderIsPayById(@Param("orderId") String orderId,@Param("transaction_id") String transaction_id,@Param("payTime") String payTime,@Param("out_trade_no") String out_trade_no);

    //修改订单的地址 系统订单表
    @Update("UPDATE system_order SET receiveName=#{receiveName},receiveTell=#{receiveTell},receiveAddress=#{receiveAddress},addressId=#{addressId} where systemId=#{systemId}")
    int updateSysAddress(@Param("receiveName") String receiveName,@Param("receiveTell") String receiveTell,@Param("receiveAddress") String receiveAddress,@Param("addressId") int addressId,@Param("systemId") String systemId);

    @Update("UPDATE orders SET receiveName=#{receiveName},receiveTell=#{receiveTell},receiveAddress=#{receiveAddress},addressId=#{addressId} where systemId=#{systemId}")
    int updateSysAddress2(@Param("receiveName") String receiveName,@Param("receiveTell") String receiveTell,@Param("receiveAddress") String receiveAddress,@Param("addressId") int addressId,@Param("systemId") String systemId);

    @Update("UPDATE orders SET receiveName=#{receiveName},receiveTell=#{receiveTell},receiveAddress=#{receiveAddress},addressId=#{addressId} where orderId=#{orderId}")
    int updateSupplierOrderAddress(@Param("receiveName") String receiveName,@Param("receiveTell") String receiveTell,@Param("receiveAddress") String receiveAddress,@Param("addressId") int addressId,@Param("orderId") String orderId);

    @Update("Update orders set orderStatus=1 WHERE systemId=#{orderId}")
    int updateSystemOrderStatus1(@Param("orderId") String orderId);

    @Update("Update orders set orderStatus=1 WHERE orderId=#{orderId}")
    int updateSystemOrderStatus2(@Param("orderId") String orderId);

    @Select("select * from template_sort where parentId=#{parentId} and type=#{type}")
    List<TemplateSort> getTempImageSort(@Param("parentId") int parentId,@Param("type") int type);

    @Select("select * from template_sort_image where sortId=#{sortId}")
    List<TemplateImage> getTempImageBySortId(@Param("sortId") int sortId);

    @Select("select * from template_sort_image where sortId in(select id from template_sort where parentId=#{sourceId} and type=#{type})")
    List<TemplateImage> getTempImageBysourceId(@Param("sourceId") int sourceId,@Param("type") int type);

    @Insert("INSERT INTO editimage (orderId,oid,zpic,fpic,num) VALUES (#{orderId},#{oid},#{zpic},#{fpic},#{num})")
    int saveEditImage(@Param("oid") int oid,@Param("orderId") String orderId,@Param("zpic") String zpic,@Param("fpic") String fpic,@Param("num") int num);

    @Insert("INSERT INTO editimage (carid,zpic,fpic,num) VALUES (#{carid},#{zpic},#{fpic},#{num})")
    int saveEditImage2(@Param("carid") int carid,@Param("zpic") String zpic,@Param("fpic") String fpic,@Param("num") int num);

    @Select("select imageUrl from marketing_poster where id=1")
    String getIndexMarketingPoster();

    @Select("select payStatus from system_order where systemId=#{orderId}")
    int getPayState1(@Param("orderId") String orderId);

    @Select("select payStatus from orders where orderId=#{orderId}")
    int getPayState2(@Param("orderId") String orderId);

    @Select("select * from wuliu where trackingNo=#{trackingNo}")
    List<WuliuDesc> getWuliuDesc(@Param("trackingNo") String trackingNo);

    @Select("select picUrl from goods_spu_images where spuId=#{spuId} and type=#{imageType} order by level desc limit 0,1")
    String getTankuangEditTipImage(@Param("spuId") int spuId,@Param("imageType") int imageType);

    @Insert("insert into orders_wait (orderId,createtime,type) values (#{orderId},#{createtime},#{type})")
    int addOrderWait(@Param("orderId") String orderId,@Param("createtime") long createtime,@Param("type") int type);

    @Select("select orderId from orders_wait where orderId=#{orderId} and type=#{type}")
    String getOrderWaitId(@Param("orderId") String orderId,@Param("type") int type);
}
